package com.mxw.mytools.service;

import com.alibaba.excel.EasyExcel;
import com.mxw.mytools.dto.ExcelReadAndProcessAndWriteRequest;
import com.mxw.mytools.dto.ExcelReadAndProcessAndWriteResponse;
import com.mxw.mytools.dto.ExcelReadResponse;
import com.mxw.mytools.dto.ExcelToDbRequestFieldProcess;
import com.mxw.mytools.helper.HeadAnalysisEventListener;
import com.mxw.mytools.mapper.CommonMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.MapUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.File;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

@Service
@Slf4j
public class ExcelService {
    @Autowired
    private CommonMapper commonMapper;

    @Transactional
    public ExcelReadAndProcessAndWriteResponse readAndProcessAndWrite(ExcelReadAndProcessAndWriteRequest request) {
        ExcelReadAndProcessAndWriteResponse excelToDbResponse = new ExcelReadAndProcessAndWriteResponse();
        ExcelReadResponse mainExcelReadData = readExcel(request.getFilePath());

        // 一 处理数据
        //处理翻译字段
        if (MapUtils.isNotEmpty(request.getConvertFieldDictMap())) {
            for (Map.Entry<String, ExcelToDbRequestFieldProcess> excelToDbRequestFieldProcessEntry : request.getConvertFieldDictMap().entrySet()) {
                String convertFieldKey = excelToDbRequestFieldProcessEntry.getKey();
                ExcelToDbRequestFieldProcess requestFieldProcess = excelToDbRequestFieldProcessEntry.getValue();
                ExcelReadResponse fieldDictConvertProcessReadData = readExcel(requestFieldProcess.getSourceDictExcelPath());
                convertDictKeyValue(fieldDictConvertProcessReadData);

                Map<String, String> dictKeyValueMap = fieldDictConvertProcessReadData.getDictKeyValueMap();
                if (MapUtils.isNotEmpty(dictKeyValueMap) && mainExcelReadData.getHeadFieldNameMap().containsKey(convertFieldKey)) {
                    //查询到字段，翻译字典数据, 并把翻译字段新增到文件中
                    //header新增
                    String convertFieldKeyNew = convertFieldKey + "_convert";
                    Integer currentMaxIndex = getMaxIndex(mainExcelReadData.getHeadIndexMap());
                    mainExcelReadData.getHeadIndexMap().put(currentMaxIndex + 1, convertFieldKeyNew);
                    mainExcelReadData.getHeadFieldNameMap().put(convertFieldKeyNew, currentMaxIndex + 1);

                    if (CollectionUtils.isNotEmpty(mainExcelReadData.getDatas())) {
                        for (Map<Integer, String> dataMap : mainExcelReadData.getDatas()) {
                            Integer mainExcelRowIndex = mainExcelReadData.getHeadFieldNameMap().get(convertFieldKey);
                            String convertFiledResult = dictKeyValueMap.get(dataMap.get(mainExcelRowIndex));
                            dataMap.put(mainExcelReadData.getHeadFieldNameMap().get(convertFieldKeyNew), convertFiledResult);
                        }
                    }
                }
            }
        }

        //delete row
        for (String deleteRowByExcelFilePath : request.getDeleteRowByExcelList()) {
            ExcelReadResponse deleteRowSourceExcel = readExcel(deleteRowByExcelFilePath);
            if (CollectionUtils.isNotEmpty(deleteRowSourceExcel.getDatas())) {
                Iterator<Map<Integer, String>> iterator = mainExcelReadData.getDatas().iterator();
                while (iterator.hasNext()) {
                    Map<Integer, String> mainRow = iterator.next();
                    //所有行都满足一致就删除
                    for (Map<Integer, String> deleteRowSourceExcelDataRow : deleteRowSourceExcel.getDatas()) {

                        boolean isAllEq = false;
                        for (Map.Entry<String, Integer> deleteRowSourceExcelHeaderEntry : deleteRowSourceExcel.getHeadFieldNameMap().entrySet()) {
                            Integer mainExcelDataRowIndex = mainExcelReadData.getHeadFieldNameMap().get(deleteRowSourceExcelHeaderEntry.getKey());

                            if (null != mainExcelDataRowIndex) {
                                String deleteFiledRowValue = deleteRowSourceExcelDataRow.get(deleteRowSourceExcelHeaderEntry.getValue());

                                String mainRowValue = mainRow.get(mainExcelDataRowIndex);

                                if (Objects.equals(deleteFiledRowValue, mainRowValue)) {
                                    isAllEq = true;
                                }
                            }


                            if (!isAllEq) {
                                break;
                            }
                        }

                        if (isAllEq) {
                            iterator.remove();
                            break;
                        }
                    }

                }
            }
        }


        //处理交集

        //处理差集

        //处理排除行


        // 二 写数据（数据库或者excel）
        writeData(mainExcelReadData, request, excelToDbResponse);

        return excelToDbResponse;
    }


    private void writeData(ExcelReadResponse dataResult, ExcelReadAndProcessAndWriteRequest request, ExcelReadAndProcessAndWriteResponse response) {
        DateTimeFormatter localTimeFormat = DateTimeFormatter.ofPattern("yyyy年MM月dd日HH时mm分ss");
        if ("db".equals(request.getResultWriteType())) {
            //1 创建表
            String tableName = new File(request.getFilePath()).getName().replace(".", "").replace(" ","") + LocalDateTime.now().format(localTimeFormat);
            response.setResultFile(tableName);

            StringBuilder sql = new StringBuilder();
            sql.append("create table ").append(tableName).append("( id int(10) unsigned  not null  primary key auto_increment comment '主键', ");

            for (int i = 0; i < dataResult.getHeadIndexMap().keySet().size(); i++) {
//               sql.append(dataResult.getHeadIndexMap().get(i)).append(" varchar(1000) CHARACTER SET utf8mb4 DEFAULT NULL, ");
               sql.append(dataResult.getHeadIndexMap().get(i)).append(" varchar(1000)  DEFAULT NULL , ");
            }

            sql.append("    delete_flag int (4)  default 0   null  comment '删除状态',\n" +
                    "    updated_time  datetime default  current_timestamp on update  current_timestamp   null comment '更新时间',\n" +
                    "    created_time datetime default  current_timestamp   null comment '创建时间'\n" +
                    ")engine =InnoDB auto_increment = 1 default charset = utf8mb4 comment = '").append(tableName).append("数据导入处理表").append("';");
            commonMapper.execSql(sql.toString());

            //2 写入数据
            StringBuilder insertSql = new StringBuilder();
            insertSql.append("insert into ").append(tableName).append("(");

            boolean isFirstHeaderFiled = true;
            for (int i = 0; i < dataResult.getHeadIndexMap().keySet().size(); i++) {
                if (isFirstHeaderFiled) {
                    isFirstHeaderFiled = false;
                }else {
                    insertSql.append(",");
                }
                insertSql.append(dataResult.getHeadIndexMap().get(i));
            }


            insertSql.append(") values ");
            boolean isFirstRow = true;
            for (Map<Integer, String> data : dataResult.getDatas()) {
                if (isFirstRow) {
                    isFirstRow = false;
                }else {
                    insertSql.append(",");
                }
                boolean isFirstFiled = true;
                insertSql.append("( ");
                for (int i = 0; i < dataResult.getHeadIndexMap().keySet().size(); i++) {
                    if (isFirstFiled) {
                        isFirstFiled = false;
                    }else {
                        insertSql.append(",");
                    }

                    String rowValue = data.get(i);
                    if (null != rowValue) {
                        rowValue = rowValue.trim();
                    }
                    if (null != rowValue) {
                        insertSql.append("'").append(rowValue).append("'");
                    }else {
                        insertSql.append("null");
                    }
                }
                insertSql.append(")");
            }

            insertSql.append(";");

            commonMapper.execSql(insertSql.toString());

        } else {
            List<List<String>> head = new ArrayList<>();
            for (int i = 0; i < dataResult.getHeadIndexMap().keySet().size(); i++) {
                head.add(Arrays.asList(dataResult.getHeadIndexMap().get(i)));
            }

            String resultFile = request.getFilePath() + LocalDateTime.now().format(localTimeFormat) + ".xlsx";
            EasyExcel.write(resultFile).head(head).sheet("sheet1").doWrite(dataResult.getDatas());
            response.setResultFile(resultFile);
        }
    }

    private void convertDictKeyValue(ExcelReadResponse excelReadResponse) {
        if (null == excelReadResponse || CollectionUtils.isEmpty(excelReadResponse.getDatas())) {
            return;
        }

        Map<String, String> objectObjectConcurrentHashMap = new ConcurrentHashMap<>();
        for (Map<Integer, String> data : excelReadResponse.getDatas()) {
            objectObjectConcurrentHashMap.put(data.get(0), data.get(1));
        }

        excelReadResponse.setDictKeyValueMap(objectObjectConcurrentHashMap);
    }

    public ExcelReadResponse readExcel(String filePath) {
        ExcelReadResponse excelReadResponse = new ExcelReadResponse();
        HeadAnalysisEventListener headAnalysisEventListener = new HeadAnalysisEventListener();

        // 这里 也可以不指定class，返回一个list，然后读取第一个sheet 同步读取会自动finish
        List<Map<Integer, String>> listMap = EasyExcel.read(filePath, headAnalysisEventListener).sheet().doReadSync();

        excelReadResponse.setHeadIndexMap(headAnalysisEventListener.getHeadMap());
        if (MapUtils.isNotEmpty(excelReadResponse.getHeadIndexMap())) {
            for (Map.Entry<Integer, String> entry : excelReadResponse.getHeadIndexMap().entrySet()) {
                excelReadResponse.getHeadFieldNameMap().put(entry.getValue(), entry.getKey());
            }
        }

        excelReadResponse.setDatas(listMap);
        return excelReadResponse;
    }


    public static Integer getMaxIndex(Map<Integer, String> headIndexMap) {
        if (MapUtils.isEmpty(headIndexMap)) {
            return 0;
        }

        return headIndexMap.keySet().stream().max(Comparator.comparing(Integer::intValue)).get();

    }

}
